package com.egao.common.module.system.transfer;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.egao.common.core.web.JsonResult;
import com.egao.common.module.company.constants.CheckRecordConstants;
import com.egao.common.module.company.constants.PreachConstants;
import com.egao.common.module.company.entity.*;
import com.egao.common.module.company.service.*;
import com.egao.common.module.push.entity.PositionMatching;
import com.egao.common.module.push.service.PositionMatchingService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 校内宣讲会数据导入
 * @author: dl
 */
@RestController
@Slf4j
public class PreachDataController {

    @Value("${old.datasource.jyurl}")
    private String url;

    @Value("${old.datasource.username}")
    private String userName;

    @Value("${old.datasource.password}")
    private String password;

    @Value("${minio.old-data-head}")
    private String contentUrl;

    @Autowired
    private CompanyService companyService;

    @Autowired
    private PreachService preachService;

    @Autowired
    private PositionService positionService;

    @Autowired
    private FieldService fieldService;

    @Autowired
    private PositionMatchingService positionMatchingService;

    @Autowired
    private FieldRecordService fieldRecordService;

    @Transactional(rollbackFor = Exception.class)
    public JsonResult transfer() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        long beginTime = System.currentTimeMillis();
        List<Position> positionList = getPositionList();
        List<Company> companyList = getCompanyList();
        List<Field> fieldList = getFieldList();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, userName, password);
            st = conn.createStatement();
            String sql = "SELECT " +
                    " id, " +
                    " preachtype, " +
                    " title, " +
                    " sponsor, " +
                    " holddate, " +
                    " holddatetime, " +
                    " HolddateInt, " +
                    " holdtime, " +
                    " fieldid, " +
                    " COMMENT, " +
                    " number, " +
                    " endtime, " +
                    " isopen, " +
                    " create_date, " +
                    " del_flag, " +
                    " create_by, " +
                    " update_by, " +
                    " update_date, " +
                    " remarks, " +
                    " companyid, " +
                    " timeone, " +
                    " timeoneint, " +
                    " timeonetime, " +
                    " timetwo, " +
                    " timetwoint, " +
                    " timetwotime, " +
                    " timethree, " +
                    " timethreeint, " +
                    " timethreetime, " +
                    " checkstate, " +
                    " checkcomment, " +
                    " checktime, " +
                    " checkuserid, " +
                    " hits, " +
                    " informationtype, " +
                    " fieldname, " +
                    " sourcetitle, " +
                    " sourceurl, " +
                    " recruitnumber, " +
                    " jobname, " +
                    " major, " +
                    " education, " +
                    " cancel, " +
                    " email, " +
                    " tel, " +
                    " recruiturl, " +
                    " linkman, " +
                    " auditor, " +
                    " ip, " +
                    " liabletel, " +
                    " personliable, " +
                    " salary, " +
                    " work_place, " +
                    " position_ids, " +
                    " position_names, " +
                    " info_list, " +
                    " audition_field_id, " +
                    " audition_hold_time, " +
                    " audition_hold_date_int, " +
                    " audition_hold_date, " +
                    " push_list, " +
                    " push_flag " +
                    "FROM " +
                    " preach where informationtype = 1 and preachtype is not null";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            log.info("总数：{}", rs.getRow());
            int i = 0;
            int j = 0;
            while (rs.next()) {
                String oldId = rs.getString("id");
                String title = rs.getString("title");
                Integer type = rs.getInt("preachtype");
                String companyName = rs.getString("sponsor");

                Date holddate = rs.getTimestamp("holddate") != null ? new Date(rs.getTimestamp("holddate").getTime()) : null;
                String holddatehour = rs.getString("holddatetime");
                String holddateType = rs.getString("holdtime");
                String fieldOldId = rs.getString("fieldid");
                String comment = rs.getString("COMMENT");
                // 希望规模
                String number = rs.getString("number");
                Integer isopen = rs.getInt("isopen");
                String del_flag = rs.getString("del_flag");
                String companyid = rs.getString("companyid");


                Integer checkstate = rs.getInt("checkstate");
                String checkstatecontent = rs.getString("checkcomment");
                Integer hits = rs.getInt("hits");

                String recruitnumber = rs.getString("recruitnumber");
                String jobname = rs.getString("jobname");
                String education = rs.getString("education");
                Integer cancel = rs.getInt("cancel");
                String majors = rs.getString("major");
                String email = rs.getString("email");
                String tel = rs.getString("tel");
                String recruiturl = rs.getString("recruiturl");
                String linkman = rs.getString("linkman");
                // 负责人电话
                String liabletel = rs.getString("liabletel");
                // 负责人
                String personliable = rs.getString("personliable");

                String position_ids = rs.getString("position_ids");
                String position_names = rs.getString("position_names");
                String salary = rs.getString("salary");
                String work_place = rs.getString("work_place");
                String info_list = rs.getString("info_list");
                String push_list = rs.getString("push_list");

                String push_flag = rs.getString("push_flag");
                Integer invalid = rs.getInt("cancel");
                Date createDate = new Date(rs.getTimestamp("create_date").getTime());
                Preach preach = new Preach();
                if (rs.getTimestamp("update_date") != null) {
                    Date updateDate = new Date(rs.getTimestamp("update_date").getTime());
                    preach.setUpdateTime(updateDate);
                }

                preach.setHits(hits);
                preach.setInvalid(invalid);
                preach.setCreateTime(createDate);
                preach.setOldId(oldId);
                preach.setTitle(title);


                preach.setHoldTime(holddate);
                preach.setChoiceHoldTimeDate(holddate);
                if (StrUtil.isNotBlank(holddatehour)) {
                    preach.setChoiceHoldTimeHour(holddatehour.replaceAll("：", ":"));
                }
                if (StrUtil.isNotBlank(holddateType)) {
                    preach.setChoiceHoldTimeType(Integer.parseInt(holddateType));
                }

                // 学校发布
                if (type == 1) {
                    preach.setAddType("2");
                    preach.setApplyTimeType("1");
                    preach.setHoldTimeOneDate(holddate);
                    preach.setHoldTimeOneHour(holddatehour.replaceAll("：", ":"));
                    preach.setHoldTimeOneType(Integer.parseInt(holddateType));
                // 单位发布
                } else if (type == 2) {
                    preach.setAddType("1");
                    Date timeone = new Date(rs.getTimestamp("timeone").getTime());
                    Integer timeoneint = rs.getInt("timeoneint");
                    String timeonetime = rs.getString("timeonetime");
                    Date timetwo = new Date(rs.getTimestamp("timetwo").getTime());
                    Integer timetwoint = rs.getInt("timetwoint");
                    String timetwotime = rs.getString("timetwotime");
                    Date timethree = new Date(rs.getTimestamp("timethree").getTime());
                    Integer timethreeint = rs.getInt("timethreeint");
                    String timethreetime = rs.getString("timethreetime");

                    preach.setHoldTimeOneDate(timeone);
                    preach.setHoldTimeOneHour(timeonetime.replaceAll("：", ":"));
                    preach.setHoldTimeOneType(timeoneint);
                    preach.setHoldTimeTwoDate(timetwo);
                    preach.setHoldTimeTwoHour(timetwotime.replaceAll("：", ":"));
                    preach.setHoldTimeTwoType(timetwoint);
                    preach.setHoldTimeThreeDate(timethree);
                    preach.setHoldTimeThreeHour(timethreetime.replaceAll("：", ":"));
                    preach.setHoldTimeThreeType(timethreeint);

                    if (holddate != null) {
                        String apply_time_type = getChoiceTime(preach);
                        if (apply_time_type != null) {
                            preach.setApplyTimeType(apply_time_type);
                        }
                    }

                // 异常
                } else {
                    preach.setAddType("3");
                }
                preach.setCompanyName(companyName);
                preach.setHoldTime(holddate);

                if (salary != null && salary.contains("~") && !salary.contains("面议")) {
                    preach.setSalaryType("0");
                    // 小写转换
                    salary = salary.toLowerCase()
                            .replaceAll("\\.", "")
                            .replaceAll("\\+", "")
                            .replaceAll("&middot;", "")
                            .replaceAll(" ", "");
                    log.info(salary);
                    // 数据转换
                    if (salary.contains("k")) {
                        salary = salary.replaceAll("k", "000");
                    } else if (salary.contains("万")) {
                        salary = salary.replaceAll("万", "0000");
                    } else if (salary.contains("元")) {
                        salary = salary.replaceAll("元", "");
                    } else if (salary.contains("w")) {
                        salary = salary.replaceAll("w", "0000");
                    }
                    String[] array = salary.split("~");
                    String min = array[0].trim();
                    String max = array[1].trim();
                    if (max.contains("不等") || max.contains("以上")) {
                        max = min;
                    }
                    preach.setSalaryMin(Integer.parseInt(min));
                    preach.setSalaryMax(Integer.parseInt(max));

                } else if ("面议".equals(salary)) {
                    preach.setSalaryType("1");
                } else if(salary != null && salary.contains("面议")) {
                    preach.setSalaryType("1");
                } else if(salary == null) {
                    preach.setSalaryType("0");
                } else {
                    // 异常数据
                    preach.setSalaryType("2");
                }


                preach.setDeleted("0".equals(del_flag) ? 0 : 1);
                preach.setState(getState(checkstate));

                preach.setContent(comment != null ? comment.replaceAll("/ueditor/jsp/upload", contentUrl + "/ueditor/jsp/upload") : "");
                preach.setScale(number);
                preach.setLeader(personliable);
                preach.setLeaderMobile(liabletel);
                preach.setJobName(jobname);
                preach.setInvalid(cancel);
                preach.setPreachPerson(info_list);
                preach.setNumber(recruitnumber);
                preach.setArea(work_place);
                preach.setMajors(majors);
                preach.setEducation(education);
                preach.setRecruitContact(linkman);
                preach.setRecruitMobile(tel);
                preach.setRecruitWebsite(recruiturl);
                preach.setEmail(email);
                preach.setStateContent(checkstatecontent);
                preach.setPositionIds(getPositionIds(positionList, position_ids));
                preach.setPositionNames(position_names);
                if (companyid != null) {
                    Company company = getCompanyId(companyList, companyid);
                    if (company != null) {
                        preach.setCompanyId(company.getId());
                    } else {
                        preach.setCompanyId(0);
                    }
                }
                preach.setPushFlag("1".equals(push_flag) ? "0" : "1");
                Integer fieldId = null;
                if (StrUtil.isNotBlank(fieldOldId)) {
                    fieldId = getFieldId(fieldList, fieldOldId);
                    preach.setFieldId(fieldId);
                }
                i ++;
                log.info("宣讲会信息：{} 数量：{} 个", title, i);
                if (preachService.save(preach)) {
                    if (fieldId != null) {
                        FieldRecord fieldRecord = new FieldRecord();
                        fieldRecord.setType(PreachConstants.fieldRecordType);
                        fieldRecord.setBusinessId(preach.getId());
                        fieldRecord.setFieldId(fieldId);
                        fieldRecord.setStartTime(holddate);
                        fieldRecord.setStartDate(holddate);
                        log.info("getChoiceHoldTimeType：{} oldId : {}", preach.getChoiceHoldTimeType(), oldId);
                        fieldRecord.setTimeType(String.valueOf(preach.getChoiceHoldTimeType()));

                        fieldRecordService.save(fieldRecord);
                    }
                    List<PositionMatching> list;
                    if (push_list == null) {
                        continue;
                    }
                    list = getPositionMatching(push_list, preach.getId(), CheckRecordConstants.CHECK_PREACH, preach.getPositionIds());
                    if (list == null) {
                        continue;
                    }
                    j ++;
                    positionMatchingService.saveBatch(list);
                    log.info("插入匹配信息：{} 数量：{} 个 旧信息： {}", title, j, push_list);
                }
            }
            long overTime = System.currentTimeMillis();
            long finishTime = (overTime - beginTime) / 1000;
            log.info("完成时间 : {} 分", finishTime);
            return JsonResult.ok(String.format("完成时间 : %s 分", finishTime));
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return JsonResult.ok();
    }


    /**
     * 查询所有单位信息
     * @return
     */
    private List<Company> getCompanyList() {
        return companyService.list();
    }

    /**
     * 查询原始
     * @return
     */
    private Company getCompanyId(List<Company> companyList, String oldId) {
        for(Company company : companyList) {
            if (oldId.equals(company.getOldId())) {
                return company;
            }
        }
        return null;
    }

    private String getState(Integer state) {
        switch (state) {
            case 0 : return "0";
            case 1 : return "1";
            case -1 : return "2";
            case -2 : return "3";
            default: return "";
        }
    }


    /**
     * 查询所有职位信息
     * @return
     */
    private List<Position> getPositionList() {
        return positionService.list();
    }

    /**
     * 查询职位信息
     * @return
     */
    private String getPositionIds(List<Position> positionList, String ids) {
        if (StrUtil.isBlank(ids)) {
            return "";
        }
        String[] arr = ids.split(",");
        StringBuilder sr = new StringBuilder();
        for (String id : arr) {
            for(Position position : positionList) {
                if (id.equals(position.getOldId())) {
                    sr.append(position.getId()).append(",");
                }
            }
        }
        return sr.deleteCharAt(sr.length() - 1).toString();
    }

    private List<PositionMatching> getPositionMatching(String json, Integer id, String type, String ids) {
        if (StrUtil.isBlank(json)) {
            return null;
        }
        log.info("json : {}", json);
        if (!JSONUtil.isJsonArray(json)) {
            log.info("json error : {}", json);
            return null;
        }
        JSONArray array = JSONUtil.parseArray(json);
        String[] arrIds = ids.split(",");
        List<PositionMatching> list = new ArrayList<>();
        int size = array.size();
        for (int i = 0; i < size; i ++) {
            PositionMatching p = new PositionMatching();
            p.setType(type);
            p.setPositionId(Integer.parseInt(arrIds[i]));
            p.setBusinessId(id);
            JSONObject j = array.getJSONObject(i);
            p.setPositionName(j.getStr("name"));
            p.setNumber(Integer.parseInt(j.getStr("type")));
            if (j.getStr("oneLabel") != null) {
                p.setMatchingOne(j.getStr("oneLabel"));
                p.setMatchingOneValue(Long.valueOf(j.getStr("one")));
            }
            if (j.getStr("twoLabel") != null) {
                p.setMatchingTwo(j.getStr("twoLabel"));
                p.setMatchingTwoValue(Long.valueOf(j.getStr("two")));
            }
            if (j.getStr("threeLabel") != null) {
                p.setMatchingThree(j.getStr("threeLabel"));
                p.setMatchingThreeValue(Long.valueOf(j.getStr("three")));
            }
            list.add(p);
        }
        return list;
    }

    /**
     * 查询所有场地信息
     * @return
     */
    private List<Field> getFieldList() {
        return fieldService.list();
    }

    /**
     * 查询场地编号
     * @return
     */
    private Integer getFieldId(List<Field> list, String oldId) {
        for (Field field : list) {
            if (oldId.equals(field.getOldId())) {
                return field.getId();
            }
        }
        return 0;
    }

    private String getChoiceTime(Preach old) {
        if ("2".equals(old.getAddType())) {
            return null;
        }
        String holdDate = DateUtil.format(old.getHoldTime(), "yyyy-MM-dd");
        String holdDateHour = old.getChoiceHoldTimeHour();
        Integer holdDateType = old.getChoiceHoldTimeType();

        String holdDate1 = DateUtil.format(old.getHoldTimeOneDate(), "yyyy-MM-dd");
        String holdDateHour1 = old.getHoldTimeOneHour();
        Integer holdDateType1 = old.getHoldTimeOneType();

        String holdDate2 = DateUtil.format(old.getHoldTimeTwoDate(), "yyyy-MM-dd");
        String holdDateHour2 = old.getHoldTimeTwoHour();
        Integer holdDateType2 = old.getHoldTimeTwoType();

        String holdDate3 = DateUtil.format(old.getHoldTimeThreeDate(), "yyyy-MM-dd");
        String holdDateHour3 = old.getHoldTimeThreeHour();
        Integer holdDateType3 = old.getHoldTimeThreeType();

        log.info("a1 : {} a2 : {} a3 : {} a4 : {} a5 : {} a6 : {} a7 : {} a8 : {} a9 : {} a10 : {} a11 : {} a12 : {}"
                , holdDate,holdDateHour,holdDateType
                , holdDate1,holdDateHour1,holdDateType1
                , holdDate2,holdDateHour2,holdDateType2
                , holdDate3,holdDateHour3,holdDateType3);

        // 处理错误数据
        if (StrUtil.isBlank(holdDateHour)) {
            holdDateHour = holdDateHour1;
        }
        // 选择第一个申请时间
        if (holdDate.equals(holdDate1) && holdDateHour.equals(holdDateHour1) && holdDateType.equals(holdDateType1)) {
            return "1";
        }
        if (holdDate.equals(holdDate2) && holdDateHour.equals(holdDateHour2) && holdDateType.equals(holdDateType2)) {
            return "2";
        }
        if (holdDate.equals(holdDate3) && holdDateHour.equals(holdDateHour3) && holdDateType.equals(holdDateType3)) {
            return "3";
        }
        return "0";
    }

    public static void main(String[] args) {
        String json = "[{'threeLabel':'80%','one':11,'name':'财务经理','oneLabel':'100%','type':3,'twoLabel':'90%','two':67,'three':120}]";
        System.out.println(JSONUtil.isJsonArray(json));
    }
}
